'''
Created on Jun 12, 2010

@author: oabalbin
'''
import MySQLdb;

class tables():
        
        def __init__(self, dbhost, dbuser, dbpasswd, database):
            """ Database Information """
            self.myhost = dbhost
            self.myuser = dbuser
            self.mypasswd = dbpasswd
            self.mydb = database
            self.create_connector()
        
        def create_connector(self):
            self.conn=MySQLdb.connect(host=self.myhost,user=self.myuser,passwd=self.mypasswd,db=self.mydb)
        
        def close_connector(self, connector):
            connector.close
        
        def create_table(self, tableName,tableDescription):
            """ It is a generic function to create tables in mRNA test_database """
            
            cursor=self.conn.cursor()
            cursor.execute('DROP TABLE IF EXISTS '+ tableName)
            table = 'CREATE TABLE '+ tableName +' ('+ tableDescription+ ' );' 
            cursor.execute(table)  
            # Load data into the table
            #loadtable = ' LOAD DATA LOCAL INFILE \"' + pathFile + '\" into table '+ tableName + ';' 
            #cursor.execute(loadtable)
            # disconnect from server
            cursor.close
            
        def load_data_table(self,tableName,pathFile):
            
            cursor=self.conn.cursor()
            loadtable = ' LOAD DATA LOCAL INFILE \"' + pathFile + '\" into table '+ tableName + ';' 
            cursor.execute(loadtable)
            cursor.close
            
        def table_existence(self, tableName):
            """
            Check if table exists
            """
            cursor=self.conn.cursor()
            
            existence = False
            query = 'show tables like \"' + tableName + '\";'
            cursor.execute(query)
            results = cursor.fetchall()
            
            if len(results) >= 1:
                existence = True
                  
            # disconnect from server
            cursor.close
            
            return existence
        
        def load_data_table_cvs(self,tableName,pathFile):
            
            cursor=self.conn.cursor()
            loadtable = ' LOAD DATA LOCAL INFILE \"' + pathFile + '\" into table '+ tableName + ' FIELDS TERMINATED BY \"'+','+'\"'+';' 
            cursor.execute(loadtable)
            cursor.close
